﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.Data;
using System.Data.SqlClient;

using System.Configuration;

namespace CURDBase
{
    /// <summary>
    /// ADO.NET通用数据类
    /// </summary>
    class SQLHelperBase
    {
        // 读取配置文件，获得连接字符串
        private static string connStr = ConfigurationManager.ConnectionStrings["connStr"].ToString();

        public static int ExecuteNonQuery(string sql)
        {
            SqlConnection conn = new SqlConnection(connStr);
            SqlCommand cmd = new SqlCommand(sql, conn);
            int ret = 0;
            try     // 将可能出现异常的代码放入
            {
                conn.Open();
                ret = cmd.ExecuteNonQuery();
                //return ret;
            }
            catch (Exception ex)
            {
                string errStr = $"{DateTime.Now} 执行public int ExecuteNonQuery(string sql)出错：{ex.Message}";
                // 将错误信息写入日志

                throw new Exception("执行public int ExecuteNonQuery(string sql)出错：" + ex.Message);
            }
            finally
            {
                conn.Close();   // 关闭连接
            }

            // 如果出了异常就不会被执行了，因为上面捕获异常后又抛了异常
            // 只要 捕获了异常（就是catch到了），即使catch{ }中什么内容都没有，也不会中断
            // 但通常不会设置空catch块
            return ret;     // 这两个return都行，出异常时都不执行。但不能写在finall中
        }

        public static Object ExecuteScalar(string sql)
        {
            Object ob;
            SqlConnection conn = new SqlConnection(connStr);
            SqlCommand cmd = new SqlCommand(sql, conn);
            try
            {
                conn.Open();
                ob = cmd.ExecuteScalar();
                return ob;
            }
            catch (Exception ex)
            {
                string errStr = $"{DateTime.Now} 执行public Object ExecuteScalar(string sql)时出错：{ex.Message}";

                throw new Exception("执行public Object ExecuteScalar(string sql)时出错" + ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }

        public static SqlDataReader ExecuteReader(string sql)
        {
            SqlConnection conn = new SqlConnection(connStr);
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader reader = null;
            try
            {
                conn.Open();
                // 这里调用ExecuteReader的重载， 传的参数使reader被关闭时对应的conn也被关闭
                reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return reader;
            }
            catch (Exception ex)
            {
                throw new Exception("执行public static SqlDataReader ExecuteReader(string sql)时出错：" + ex.Message);
            }

            // reader和conn都需要被关闭，reader传出去了，方便关闭。
            // 而conn是局部变量，不好关闭
        }

        public static DataSet GetDataSet(string sql)
        {
            SqlConnection conn = new SqlConnection(connStr);
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            try
            {
                conn.Open();
                da.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception("执行public DataSet e(string sql)时出错：" + ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }

        // 重载。  实现读取多个结果集
        public static DataSet GetDataSet(Dictionary<string, string> dicTableAndSql)
        {
            SqlConnection conn = new SqlConnection(connStr);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;          // 这里就得显示编写
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            try
            {
                conn.Open();
                foreach(string tbName in dicTableAndSql.Keys)   // 遍历字典的Key集合
                {
                    // 显示编写
                    cmd.CommandText = dicTableAndSql[tbName];   // 根据key获得对应的value 即sql
                    da.Fill(ds, tbName);
                }
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception("执行public static DataSet GetDataSet(Dictionary<string, string> dicTableAndSql)时出错：" + ex.Message);
            }
            finally
            {
                conn.Close();
            }

        }
    }
}
